/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package kn.cstf2.cop.Server;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import kn.cstf2.cop.Bean.*;
import kn.cstf2.cop.Sql.CopSql;
import kn.cstf2.global.Utils.CstF2Exception;
import kn.cstf2.global.db.Operations.OperacionesDB;
import kn.cstf2.implementation.Bean.ClienteStkBean;
import kn.cstf2.implementation.Bean.T_asignStkBean;
import kn.cstf2.implementation.Sql.ImplementationSql;
import kn.cstf2.transition.Bean.TransitionBean;

/**
 *
 * @author user1
 */
public class CopServer {
    

    OperacionesDB odb = new OperacionesDB();
    
    public ArrayList<ContactsBean> getContactList(int idCop) {        
        ArrayList<ContactsBean> contacts = new ArrayList<ContactsBean>();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sentenciaSQL = CopSql.SELECT_DATOS_FROM_CONTACT;
        int index = 1;
        
        try {
            conn = odb.obtenerConexion("db");
            ps = conn.prepareStatement(sentenciaSQL);
            ps.setInt(index++, idCop);
            
            rs = ps.executeQuery();
            while (rs.next()) {
                ContactsBean contactsBean = AdaptadorCop.obtenerContact(rs);
                contacts.add(contactsBean);                
            }
        } catch (SQLException ex) {
   
            throw new CstF2Exception("Error al obtener el Usuario desde la base de datos");
        } finally {
            odb.cerrarResultSet(rs);
            odb.cerrarStatement(ps);
            odb.cerrarConnection(conn);
        }
        return contacts;
        
    }
    
    public CopBean obtenerDataFromCop(Integer id, Boolean esportacion) {
        CopBean copBean = new CopBean();
        
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sentenciaSQL = CopSql.SELECCIONAR_COP;
        int index = 1;
        
        try {
            conn = odb.obtenerConexion("db");
            ps = conn.prepareStatement(sentenciaSQL);
            ps.setInt(index++, id);
            ps.setBoolean(index++, esportacion);
            
            rs = ps.executeQuery();
            copBean = AdaptadorCop.obtenerDataFromCop(rs);
        } catch (SQLException ex) {
            throw new CstF2Exception("Error al obtener el Usuario desde la base de datos");
        } finally {
            odb.cerrarResultSet(rs);
            odb.cerrarStatement(ps);
            odb.cerrarConnection(conn);
        }
        return copBean;
    }
    
    public T_customer_solutionBeanSTK obtenerCustomerSolutionStk(Integer knkey) {
        T_customer_solutionBeanSTK t_customer_solutionBeanSTK = new T_customer_solutionBeanSTK();
        
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sentenciaSQL = CopSql.SELECCIONAR_CUSTOMSOLUTION_FROMSTK;
        int index = 1;
        
        try {
            conn = odb.obtenerConexion("db2");
            ps = conn.prepareStatement(sentenciaSQL);
            ps.setInt(index++, knkey);
            
            
            rs = ps.executeQuery();
            t_customer_solutionBeanSTK = AdaptadorCop.obtenerCustomerSolutionStk(rs);
        } catch (SQLException ex) {
            throw new CstF2Exception("Error al obtener el Usuario desde la base de datos");
        } finally {
            odb.cerrarResultSet(rs);
            odb.cerrarStatement(ps);
            odb.cerrarConnection(conn);
        }
        return t_customer_solutionBeanSTK;
    }
    
    public void insertarValoresDefaultEnCop(Boolean esportacion, Integer id) {
        CopBean copBean = new CopBean();
        
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sentenciaSQL = CopSql.INSETAR_DATOS_DEFAULT_ENCOP;
        int index = 1;
        
        try {
            conn = odb.obtenerConexion("db");
            ps = conn.prepareStatement(sentenciaSQL);
            
            ps.setBoolean(index++, esportacion);
            ps.setInt(index++, id);
            
            ps.execute();
            //copBean = Adaptador.obtenerDataFromCop(rs);         
        } catch (SQLException ex) {
            throw new CstF2Exception("Error al obtener el Usuario desde la base de datos");
        } finally {
            //odb.cerrarResultSet(rs);
            odb.cerrarStatement(ps);
            odb.cerrarConnection(conn);
        }
        //return copBean;
    }
    
    public ClienteStkBean obtenerTodosDatosFromClientesSTK(Integer knkey) {
        
        ClienteStkBean clienteStkBean = new ClienteStkBean();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sentenciaSQL = CopSql.SELECCIONAR_ALLDATACLIENTFROMSTK;
        int index = 1;
        
        try {
            conn = odb.obtenerConexion("db2");
            ps = conn.prepareStatement(sentenciaSQL);
            ps.setInt(index++, knkey);
            
            rs = ps.executeQuery();
            clienteStkBean = kn.cstf2.implementation.Server.AdaptadorImple.obtenerTodosDatosFromClientesSTK(rs);
        } catch (SQLException ex) {
          
            throw new CstF2Exception("Error al obtener el Usuario desde la base de datos");
        } finally {
            odb.cerrarResultSet(rs);
            odb.cerrarStatement(ps);
            odb.cerrarConnection(conn);
        }
        return clienteStkBean;
    }
    
    public ClienteStkBean obtenerAcountVisibilitySTK(Integer knkey) {
        
        ClienteStkBean clienteStkBean = new ClienteStkBean();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sentenciaSQL = CopSql.SELECCIONAR_ACCOUNTVISIBILITYFROMSTK;
        int index = 1;
        
        try {
            conn = odb.obtenerConexion("db2");
            ps = conn.prepareStatement(sentenciaSQL);
            ps.setInt(index++, knkey);
            
            rs = ps.executeQuery();
            clienteStkBean = kn.cstf2.implementation.Server.AdaptadorImple.obtenerAcountVisibilitySTK(rs);
        } catch (SQLException ex) {
            throw new CstF2Exception("Error al obtener el Usuario desde la base de datos");
        } finally {
            odb.cerrarResultSet(rs);
            odb.cerrarStatement(ps);
            odb.cerrarConnection(conn);
        }
        return clienteStkBean;
    }
    
    public CopBean obtenerIdDesdeCop(Integer id) {
        
        CopBean copBean = new CopBean();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sentenciaSQL = CopSql.SELECCIONAR_IDyEXPORT_FROM_COP;
        int index = 1;
        
        try {
            conn = odb.obtenerConexion("db");
            ps = conn.prepareStatement(sentenciaSQL);
            ps.setInt(index++, id);
            
            rs = ps.executeQuery();
            copBean = AdaptadorCop.obtenerIdDesdeCop(rs);
        } catch (SQLException ex) {
              throw new CstF2Exception("Error al obtener el Usuario desde la base de datos");
        } finally {
            odb.cerrarResultSet(rs);
            odb.cerrarStatement(ps);
            odb.cerrarConnection(conn);
        }
        return copBean;
    }
    
    public ArrayList<ExceptionBean> obtenerExceptions(Integer idcop) {
        
        ExceptionBean exceptionBean = new ExceptionBean();
        ArrayList<ExceptionBean> exceptionBeanList = new ArrayList<ExceptionBean>();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sentenciaSQL = CopSql.SELECCT_EXCEPTIONS_FROMSTK;
        int index = 1;
        
        try {
            conn = odb.obtenerConexion("db");
            ps = conn.prepareStatement(sentenciaSQL);
            ps.setInt(index++, idcop);
            
            rs = ps.executeQuery();
            exceptionBeanList = AdaptadorCop.obtenerExceptions(rs);
        } catch (SQLException ex) {
           throw new CstF2Exception("Error al obtener el Usuario desde la base de datos");
        } finally {
            odb.cerrarResultSet(rs);
            odb.cerrarStatement(ps);
            odb.cerrarConnection(conn);
        }
        return exceptionBeanList;
    }
    
    public void insertarExceptions(ExceptionBean exceptioBean, Integer copbeanID) {
        
        Connection conn = null;
        PreparedStatement ps = null;
        
        String sentenciaSQL = CopSql.INSERT_EXCEPTIONS_FROMSTK;
        int index = 1;
        
        try {
            conn = odb.obtenerConexion("db");
            ps = conn.prepareStatement(sentenciaSQL);
            ps.setInt(index++, copbeanID);
            ps.setString(index++, exceptioBean.getExceptcode());
            ps.setString(index++, exceptioBean.getExceptlogic());
            
            ps.execute();
            
        } catch (SQLException ex) {

            throw new CstF2Exception("Error al obtener el Usuario desde la base de datos");
        } finally {
            odb.cerrarStatement(ps);
            odb.cerrarConnection(conn);
        }
    }
    
    public void insertarKpis(KpiBean kpiBean, Integer idcop) {
        
        Connection conn = null;
        PreparedStatement ps = null;
        
        String sentenciaSQL = CopSql.INSERT_KPIS_FROMSTK;
        int index = 1;
        
        try {
            conn = odb.obtenerConexion("db");
            ps = conn.prepareStatement(sentenciaSQL);
            ps.setInt(index++, idcop);
            ps.setString(index++, kpiBean.getKpicode());
            ps.setString(index++, kpiBean.getKpilogic());
            
            ps.execute();
            
        } catch (SQLException ex) {

            throw new CstF2Exception("Error al obtener el Usuario desde la base de datos");
        } finally {
            odb.cerrarStatement(ps);
            odb.cerrarConnection(conn);
        }
    }
    
    public ArrayList<KpiBean> obtenerKpis(Integer idcop) {
        
        ArrayList<KpiBean> kpiBeanList = new ArrayList<KpiBean>();
        
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sentenciaSQL = CopSql.SELECCT_KPIS_FROMSTK;
        int index = 1;
        
        try {
            conn = odb.obtenerConexion("db");
            ps = conn.prepareStatement(sentenciaSQL);
            ps.setInt(index++, idcop);
            
            rs = ps.executeQuery();
            kpiBeanList = AdaptadorCop.obtenerKpis(rs);
        } catch (SQLException ex) {
            throw new CstF2Exception("Error al obtener el Usuario desde la base de datos");
        } finally {
            odb.cerrarResultSet(rs);
            odb.cerrarStatement(ps);
            odb.cerrarConnection(conn);
        }
        return kpiBeanList;
    }
    
    public void insertarEnContats(ContactsBean contactsBean) {

        //ContactsBean contactsBean = new ContactsBean();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sentenciaSQL = CopSql.INSERT_INTO_CONTACTS;
        int index = 1;
        
        try {
            conn = odb.obtenerConexion("db");
            ps = conn.prepareStatement(sentenciaSQL);
            //ps.setInt(index++, contactsBean.getId());
            ps.setInt(index++, contactsBean.getIdcop());
            ps.setString(index++, contactsBean.getType());
            ps.setString(index++, contactsBean.getName());
            ps.setString(index++, contactsBean.getPosition());
            ps.setString(index++, contactsBean.getCompany());
            ps.setString(index++, contactsBean.getEmail());
            ps.setString(index++, contactsBean.getPhone());
            ps.setString(index++, contactsBean.getExtention());
            ps.setString(index++, contactsBean.getMobile());
            
            ps.execute();
            //contactsBean = Adaptador.insertarEnContats(rs);         
        } catch (SQLException ex) {
            
            throw new CstF2Exception("Error al obtener el Usuario desde la base de datos");
        } finally {
            //odb.cerrarResultSet(rs);
            odb.cerrarStatement(ps);
            odb.cerrarConnection(conn);
        }
        //return contactsBean;
    }
    
    public void actualizarContactsyContacsDateIntoTransitions(TransitionBean transitionBean) {

        //ContactsBean contactsBean = new ContactsBean();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sentenciaSQL = CopSql.UPDATE_CONTACTS_CONTACTSDATE_TRANSITIONS;
        int index = 1;
        
        try {
            conn = odb.obtenerConexion("db");
            ps = conn.prepareStatement(sentenciaSQL);
            ps.setBoolean(index++, transitionBean.getContacts());
            ps.setString(index++, transitionBean.getContactsdate());
            ps.setInt(index++, transitionBean.getId());
            
            
            ps.executeUpdate();
            //contactsBean = Adaptador.insertarEnContats(rs);         
        } catch (SQLException ex) {
            
            throw new CstF2Exception("Error al obtener el Usuario desde la base de datos");
        } finally {
            //odb.cerrarResultSet(rs);
            odb.cerrarStatement(ps);
            odb.cerrarConnection(conn);
        }
        //return contactsBean;
    }
    
    public ContactsBean obtenerDatosDeContacts(Integer idCop) {
        
        ContactsBean contactsBean = new ContactsBean();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sentenciaSQL = CopSql.SELECT_DATOS_FROM_CONTACT;
        int index = 1;
        
        try {
            conn = odb.obtenerConexion("db");
            ps = conn.prepareStatement(sentenciaSQL);
            ps.setInt(index++, idCop);
            
            rs = ps.executeQuery();
            contactsBean = AdaptadorCop.obtenerDatosDeContacts(rs);
        } catch (SQLException ex) {
            
            throw new CstF2Exception("Error al obtener el Usuario desde la base de datos");
        } finally {
            odb.cerrarResultSet(rs);
            odb.cerrarStatement(ps);
            odb.cerrarConnection(conn);
        }
        return contactsBean;
    }
    
    public void removeContacto(Integer id) {

        //ContactsBean contactsBean = new ContactsBean();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sentenciaSQL = "DELETE FROM c_contacts WHERE id = ?  ";
        int index = 1;
        
        try {
            conn = odb.obtenerConexion("db");
            ps = conn.prepareStatement(sentenciaSQL);
            ps.setInt(index++, id);
            
            ps.execute();
            //contactsBean = Adaptador.obtenerDatosDeContacts(rs);         
        } catch (SQLException ex) {
            
            throw new CstF2Exception("Error al obtener el Usuario desde la base de datos");
        } finally {
            //odb.cerrarResultSet(rs);
            odb.cerrarStatement(ps);
            odb.cerrarConnection(conn);
        }
        //return contactsBean;
    }
    
    public void eliminarContacto(Integer idCop) {

        //ContactsBean contactsBean = new ContactsBean();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sentenciaSQL = CopSql.DELETE_CONTACT;
        int index = 1;
        
        try {
            conn = odb.obtenerConexion("db");
            ps = conn.prepareStatement(sentenciaSQL);
            ps.setInt(index++, idCop);
            
            ps.execute();
            //contactsBean = Adaptador.obtenerDatosDeContacts(rs);         
        } catch (SQLException ex) {
            
            throw new CstF2Exception("Error al obtener el Usuario desde la base de datos");
        } finally {
            //odb.cerrarResultSet(rs);
            odb.cerrarStatement(ps);
            odb.cerrarConnection(conn);
        }
        //return contactsBean;
    }
    
    public Integer contarContactosEnTransitions(Integer idTransition) {
        
        Integer count = 0;
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sentenciaSQL = CopSql.COUNT_CONTACTS;
        int index = 1;
        
        try {
            conn = odb.obtenerConexion("db");
            ps = conn.prepareStatement(sentenciaSQL);
            ps.setInt(index++, idTransition);
            
            rs = ps.executeQuery();
            count = AdaptadorCop.obtenerCount(rs);
        } catch (SQLException ex) {
            
            throw new CstF2Exception("Error al obtener el Usuario desde la base de datos");
        } finally {
            odb.cerrarResultSet(rs);
            odb.cerrarStatement(ps);
            odb.cerrarConnection(conn);
        }
        return count;
    }
    
    public void deshabilitarContactos(Integer idTransition) {
        
        Integer count = 0;
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sentenciaSQL = CopSql.DESABLE_CONTACTS;
        int index = 1;
        
        try {
            conn = odb.obtenerConexion("db");
            ps = conn.prepareStatement(sentenciaSQL);
            ps.setInt(index++, idTransition);
            
            ps.executeUpdate();
            //count = Adaptador.obtenerCount(rs);         
        } catch (SQLException ex) {
            
            throw new CstF2Exception("Error al obtener el Usuario desde la base de datos");
        } finally {
            //odb.cerrarResultSet(rs);
            odb.cerrarStatement(ps);
            odb.cerrarConnection(conn);
        }
        //return count;
    }
    
    public T_asignStkBean obtenerIfOwnerFromSTK(Integer knkey) {
        
        T_asignStkBean t_asignStkBean = new T_asignStkBean();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sentenciaSQL = ImplementationSql.SELECCIONAR_IFOWNERFROMKNKEY;
        int index = 1;
        
        try {
            conn = odb.obtenerConexion("db2");
            ps = conn.prepareStatement(sentenciaSQL);
            ps.setInt(index++, knkey);
            
            rs = ps.executeQuery();
            t_asignStkBean = AdaptadorCop.obtenerIfOwnerFromSTK(rs);
        } catch (SQLException ex) {
            
            throw new CstF2Exception("Error al obtener el Usuario desde la base de datos");
        } finally {
            odb.cerrarResultSet(rs);
            odb.cerrarStatement(ps);
            odb.cerrarConnection(conn);
        }
        return t_asignStkBean;
    }
    
    public void UpdateReportingTransition(TransitionsBeanCop transitionsBeanCop) {

        //T_asignStkBean t_asignStkBean = new T_asignStkBean();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sentenciaSQL = CopSql.UPDATE_REPORTING_TRANSITIONS;
        int index = 1;
        
        try {
            conn = odb.obtenerConexion("db");
            ps = conn.prepareStatement(sentenciaSQL);
            
            ps.setBoolean(index++, transitionsBeanCop.getReporting());
            ps.setString(index++, transitionsBeanCop.getReportingdate());
            ps.setInt(index++, transitionsBeanCop.getId());
            
            ps.executeUpdate();
            //t_asignStkBean = Adaptador.obtenerIfOwnerFromSTK(rs);         
        } catch (SQLException ex) {
            
            throw new CstF2Exception("Error al obtener el Usuario desde la base de datos");
        } finally {
            //odb.cerrarResultSet(rs);
            odb.cerrarStatement(ps);
            odb.cerrarConnection(conn);
        }
        //return t_asignStkBean;
    }
    
    public void UpdateScopeOfServiceTransition(TransitionsBeanCop transitionsBeanCop) {
        
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sentenciaSQL = CopSql.UPDATE_SCOPEOFSERVICE_TRANSITIONS;
        int index = 1;
        
        try {
            conn = odb.obtenerConexion("db");
            ps = conn.prepareStatement(sentenciaSQL);
            
            ps.setBoolean(index++, transitionsBeanCop.getScopeofservice());
            ps.setString(index++, transitionsBeanCop.getScopeofservicedate());
            ps.setInt(index++, transitionsBeanCop.getId());
            
            ps.executeUpdate();
            //t_asignStkBean = Adaptador.obtenerIfOwnerFromSTK(rs);         
        } catch (SQLException ex) {
            
            throw new CstF2Exception("Error al obtener el Usuario desde la base de datos");
        } finally {
            //odb.cerrarResultSet(rs);
            odb.cerrarStatement(ps);
            odb.cerrarConnection(conn);
        }
    }
    
    public void UpdateConditionTransition(TransitionsBeanCop transitionsBeanCop) {
        
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sentenciaSQL = CopSql.UPDATE_CONDITION_TRANSITIONS;
        int index = 1;
        
        try {
            conn = odb.obtenerConexion("db");
            ps = conn.prepareStatement(sentenciaSQL);
            
            ps.setBoolean(index++, transitionsBeanCop.getCondition());
            ps.setString(index++, transitionsBeanCop.getConditionsdate());
            ps.setInt(index++, transitionsBeanCop.getId());
            
            ps.executeUpdate();
            //t_asignStkBean = Adaptador.obtenerIfOwnerFromSTK(rs);         
        } catch (SQLException ex) {
            
            throw new CstF2Exception("Error al obtener el Usuario desde la base de datos");
        } finally {
            //odb.cerrarResultSet(rs);
            odb.cerrarStatement(ps);
            odb.cerrarConnection(conn);
        }
    }
    
    public void UpdateCielIdTransition(TransitionsBeanCop transitionsBeanCop) {
        
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sentenciaSQL = CopSql.UPDATE_CIELID_TRANSITIONS;
        int index = 1;
        
        try {
            conn = odb.obtenerConexion("db");
            ps = conn.prepareStatement(sentenciaSQL);
            ps.setInt(index++, transitionsBeanCop.getId());
            ps.setBoolean(index++, transitionsBeanCop.getCielid());
            
            ps.executeUpdate();
            //t_asignStkBean = Adaptador.obtenerIfOwnerFromSTK(rs);         
        } catch (SQLException ex) {
            
            throw new CstF2Exception("Error al obtener el Usuario desde la base de datos");
        } finally {
            //odb.cerrarResultSet(rs);
            odb.cerrarStatement(ps);
            odb.cerrarConnection(conn);
        }
    }
    
    public void UpdateCielQModuleTransition(TransitionsBeanCop transitionsBeanCop) {
        
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sentenciaSQL = CopSql.UPDATE_CIELQMODULE_TRANSITIONS;
        int index = 1;
        
        try {
            conn = odb.obtenerConexion("db");
            ps = conn.prepareStatement(sentenciaSQL);
            ps.setInt(index++, transitionsBeanCop.getId());
            ps.setBoolean(index++, transitionsBeanCop.getCielqmodule());
            
            ps.executeUpdate();
            //t_asignStkBean = Adaptador.obtenerIfOwnerFromSTK(rs);         
        } catch (SQLException ex) {
            
            throw new CstF2Exception("Error al obtener el Usuario desde la base de datos");
        } finally {
            //odb.cerrarResultSet(rs);
            odb.cerrarStatement(ps);
            odb.cerrarConnection(conn);
        }
    }
    
    public void UpdateAconTransition(TransitionsBeanCop transitionsBeanCop) {
        
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sentenciaSQL = CopSql.UPDATE_ACON_TRANSITIONS;
        int index = 1;
        
        try {
            conn = odb.obtenerConexion("db");
            ps = conn.prepareStatement(sentenciaSQL);
            ps.setInt(index++, transitionsBeanCop.getId());
            ps.setBoolean(index++, transitionsBeanCop.getAcon());
            
            ps.executeUpdate();
            //t_asignStkBean = Adaptador.obtenerIfOwnerFromSTK(rs);         
        } catch (SQLException ex) {
            
            throw new CstF2Exception("Error al obtener el Usuario desde la base de datos");
        } finally {
            //odb.cerrarResultSet(rs);
            odb.cerrarStatement(ps);
            odb.cerrarConnection(conn);
        }
    }
    
    public void UpdateCarrierCCTransition(TransitionsBeanCop transitionsBeanCop) {
        
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sentenciaSQL = CopSql.UPDATE_CARRIERCC_TRANSITIONS;
        int index = 1;
        
        try {
            conn = odb.obtenerConexion("db");
            ps = conn.prepareStatement(sentenciaSQL);
            ps.setInt(index++, transitionsBeanCop.getId());
            ps.setBoolean(index++, transitionsBeanCop.getCarriercc());
            
            ps.executeUpdate();
            //t_asignStkBean = Adaptador.obtenerIfOwnerFromSTK(rs);         
        } catch (SQLException ex) {
            
            throw new CstF2Exception("Error al obtener el Usuario desde la base de datos");
        } finally {
            //odb.cerrarResultSet(rs);
            odb.cerrarStatement(ps);
            odb.cerrarConnection(conn);
        }
    }
    
    public void UpdateWorkingSTTransition(TransitionsBeanCop transitionsBeanCop) {
        
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sentenciaSQL = CopSql.UPDATE_WORKINGST_TRANSITIONS;
        int index = 1;
        
        try {
            conn = odb.obtenerConexion("db");
            ps = conn.prepareStatement(sentenciaSQL);
            
            ps.setBoolean(index++, transitionsBeanCop.getWorkingst());
            ps.setString(index++, transitionsBeanCop.getWorkingstdate());
            ps.setInt(index++, transitionsBeanCop.getId());
            
            ps.executeUpdate();
            //t_asignStkBean = Adaptador.obtenerIfOwnerFromSTK(rs);         
        } catch (SQLException ex) {
            
            throw new CstF2Exception("Error al obtener el Usuario desde la base de datos");
        } finally {
            //odb.cerrarResultSet(rs);
            odb.cerrarStatement(ps);
            odb.cerrarConnection(conn);
        }
    }
    
    public void UpdateSopTransition(TransitionsBeanCop transitionsBeanCop) {
        
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sentenciaSQL = CopSql.UPDATE_SOP_TRANSITIONS;
        int index = 1;
        
        try {
            conn = odb.obtenerConexion("db");
            ps = conn.prepareStatement(sentenciaSQL);
            ps.setInt(index++, transitionsBeanCop.getId());
            ps.setBoolean(index++, transitionsBeanCop.getSop());
            
            ps.executeUpdate();
            //t_asignStkBean = Adaptador.obtenerIfOwnerFromSTK(rs);         
        } catch (SQLException ex) {
            
            throw new CstF2Exception("Error al obtener el Usuario desde la base de datos");
        } finally {
            odb.cerrarResultSet(rs);
            odb.cerrarStatement(ps);
            odb.cerrarConnection(conn);
        }
    }
    
    public void UpdateKpiTransition(TransitionsBeanCop transitionsBeanCop) {
        
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sentenciaSQL = CopSql.UPDATE_KPI_TRANSITIONS;
        int index = 1;
        
        try {
            conn = odb.obtenerConexion("db");
            ps = conn.prepareStatement(sentenciaSQL);
            
            ps.setBoolean(index++, transitionsBeanCop.getKpi());
            ps.setString(index++, transitionsBeanCop.getKpidate());
            ps.setInt(index++, transitionsBeanCop.getId());
            
            ps.executeUpdate();
            //t_asignStkBean = Adaptador.obtenerIfOwnerFromSTK(rs);         
        } catch (SQLException ex) {
            
            throw new CstF2Exception("Error al obtener el Usuario desde la base de datos");
        } finally {
            //odb.cerrarResultSet(rs);
            odb.cerrarStatement(ps);
            odb.cerrarConnection(conn);
        }
    }
    
    public void UpdateBillingTransition(TransitionsBeanCop transitionsBeanCop) {
        
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sentenciaSQL = CopSql.UPDATE_BILLING_TRANSITIONS;
        int index = 1;
        
        try {
            conn = odb.obtenerConexion("db");
            ps = conn.prepareStatement(sentenciaSQL);
            
            ps.setBoolean(index++, transitionsBeanCop.getBilling());
            ps.setString(index++, transitionsBeanCop.getBillingdate());
            ps.setInt(index++, transitionsBeanCop.getId());
            
            ps.executeUpdate();
            //t_asignStkBean = Adaptador.obtenerIfOwnerFromSTK(rs);         
        } catch (SQLException ex) {
            
            throw new CstF2Exception("Error al obtener el Usuario desde la base de datos");
        } finally {
            //odb.cerrarResultSet(rs);
            odb.cerrarStatement(ps);
            odb.cerrarConnection(conn);
        }
    }
    
    public void UpdateClienteSTK(ClienteStkBean clienteStkBean) {
        
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sentenciaSQL = CopSql.UPDATE_CLIENTESTK;
        int index = 1;
        
        try {
            conn = odb.obtenerConexion("db");
            ps = conn.prepareStatement(sentenciaSQL);
            ps.setInt(index++, clienteStkBean.getKnkey());
            
            ps.executeUpdate();
            //t_asignStkBean = Adaptador.obtenerIfOwnerFromSTK(rs);         
        } catch (SQLException ex) {
            
            throw new CstF2Exception("Error al obtener el Usuario desde la base de datos");
        } finally {
            //odb.cerrarResultSet(rs);
            odb.cerrarStatement(ps);
            odb.cerrarConnection(conn);
        }
    }
    
    public void UpdateContactsTransition(TransitionsBeanCop transitionsBeanCop) {
        
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sentenciaSQL = CopSql.UPDATE_CONTACTS_TRANSITIONS;
        int index = 1;
        
        try {
            conn = odb.obtenerConexion("db");
            ps = conn.prepareStatement(sentenciaSQL);
            
            ps.setBoolean(index++, transitionsBeanCop.getContacts());
            ps.setInt(index++, transitionsBeanCop.getId());
            
            ps.executeUpdate();
            //t_asignStkBean = Adaptador.obtenerIfOwnerFromSTK(rs);         
        } catch (SQLException ex) {
            
            throw new CstF2Exception("Error al obtener el Usuario desde la base de datos");
        } finally {
            //odb.cerrarResultSet(rs);
            odb.cerrarStatement(ps);
            odb.cerrarConnection(conn);
        }
    }
    
    public void UpdateCop(CopBean copBean) {
        
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sentenciaSQL = CopSql.UPDATE_COP;
        int index = 1;
        
        try {
            conn = odb.obtenerConexion("db");
            ps = conn.prepareStatement(sentenciaSQL);
            ps.setString(index++, copBean.getOverview());
            ps.setBoolean(index++, copBean.getFcl());
            ps.setBoolean(index++, copBean.getLcl());
            ps.setBoolean(index++, copBean.getPerishables());
            ps.setBoolean(index++, copBean.getBrokerage());
            ps.setBoolean(index++, copBean.getIntermodal());
            ps.setBoolean(index++, copBean.getInsurance());
            ps.setBoolean(index++, copBean.getOceanfreight());
            ps.setBoolean(index++, copBean.getBlueancorline());
            ps.setBoolean(index++, copBean.getSeatransport());
            ps.setBoolean(index++, copBean.getExportcustoms());
            ps.setBoolean(index++, copBean.getInbondexp());
            ps.setBoolean(index++, copBean.getImportcustoms());
            ps.setBoolean(index++, copBean.getInbondim());
            ps.setBoolean(index++, copBean.getPreallmotor());
            ps.setBoolean(index++, copBean.getPrecombined());
            ps.setBoolean(index++, copBean.getOnallmotor());
            ps.setBoolean(index++, copBean.getOncombined());
            ps.setString(index++, copBean.getOriginconditions());
            ps.setString(index++, copBean.getDestconditions());
            ps.setString(index++, copBean.getWorkinst());
            ps.setInt(index++, copBean.getAttachedfiles());
            ps.setString(index++, copBean.getRequirements());
            ps.setBoolean(index++, copBean.getPickuptosiling());
            ps.setBoolean(index++, copBean.getCustomsclearance());
            ps.setBoolean(index++, copBean.getMaincarriage());
            ps.setBoolean(index++, copBean.getArrivaltocustoms());
            ps.setBoolean(index++, copBean.getArrivaltodelivery());
            ps.setBoolean(index++, copBean.getEscortkn());
            ps.setBoolean(index++, copBean.getEscortclient());
            ps.setString(index++, copBean.getProtocol());
            ps.setBoolean(index++, copBean.getPrecarrier());
            ps.setBoolean(index++, copBean.getOncarriage());
            ps.setInt(index++, copBean.getIdtransitioncop());
            ps.setBoolean(index++, copBean.getExport());
            
            ps.executeUpdate();
            //t_asignStkBean = Adaptador.obtenerIfOwnerFromSTK(rs);         
        } catch (SQLException ex) {
            
            throw new CstF2Exception("Error al obtener el Usuario desde la base de datos");
        } finally {
            //odb.cerrarResultSet(rs);
            odb.cerrarStatement(ps);
            odb.cerrarConnection(conn);
        }
    }
    
    public TransitionsBeanCop SelectAllDataFromTransition(Integer id) {
        
        TransitionsBeanCop transitionsBeanCop = new TransitionsBeanCop();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sentenciaSQL = CopSql.SELECT_ALLDATA_FROM_TRANSITIONS;
        int index = 1;
        
        try {
            conn = odb.obtenerConexion("db");
            ps = conn.prepareStatement(sentenciaSQL);
            ps.setInt(index++, id);
            
            rs = ps.executeQuery();
            transitionsBeanCop = AdaptadorCop.obtenerAllDataFromTransitions(rs);
        } catch (SQLException ex) {
            
            throw new CstF2Exception("Error al obtener el Usuario desde la base de datos");
        } finally {
            odb.cerrarResultSet(rs);
            odb.cerrarStatement(ps);
            odb.cerrarConnection(conn);
        }
        return transitionsBeanCop;
    }
    
    public void UpdateTransition(TransitionsBeanCop transitionsBeanCop) {

        //TransitionsBeanCop transitionsBeanCop = new TransitionsBeanCop();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sentenciaSQL = CopSql.UPDATE_TRANSITIONS;
        int index = 1;
        
        try {
            conn = odb.obtenerConexion("db");
            ps = conn.prepareStatement(sentenciaSQL);
            ps.setInt(index++, transitionsBeanCop.getKnkey());
            ps.setString(index++, transitionsBeanCop.getStatus());
            ps.setString(index++, transitionsBeanCop.getLog());
            ps.setBoolean(index++, transitionsBeanCop.getContacts());
            ps.setString(index++, transitionsBeanCop.getContactsdate());
            ps.setBoolean(index++, transitionsBeanCop.getReporting());
            ps.setString(index++, transitionsBeanCop.getReportingdate());
            ps.setBoolean(index++, transitionsBeanCop.getScopeofservice());
            ps.setString(index++, transitionsBeanCop.getScopeofservicedate());
            ps.setBoolean(index++, transitionsBeanCop.getCondition());
            ps.setString(index++, transitionsBeanCop.getConditionsdate());
            ps.setBoolean(index++, transitionsBeanCop.getCielid());
            ps.setString(index++, transitionsBeanCop.getCieliddate());
            ps.setBoolean(index++, transitionsBeanCop.getCielqmodule());
            ps.setString(index++, transitionsBeanCop.getCielqmoduledate());
            ps.setBoolean(index++, transitionsBeanCop.getAcon());
            ps.setString(index++, transitionsBeanCop.getAcondate());
            ps.setBoolean(index++, transitionsBeanCop.getCarriercc());
            ps.setString(index++, transitionsBeanCop.getCarrierccdate());
            ps.setBoolean(index++, transitionsBeanCop.getWorkingst());
            ps.setString(index++, transitionsBeanCop.getWorkingstdate());
            ps.setBoolean(index++, transitionsBeanCop.getSop());
            ps.setString(index++, transitionsBeanCop.getSopdate());
            ps.setBoolean(index++, transitionsBeanCop.getKpi());
            ps.setString(index++, transitionsBeanCop.getKpidate());
            ps.setBoolean(index++, transitionsBeanCop.getBilling());
            ps.setString(index++, transitionsBeanCop.getBillingdate());
            ps.setInt(index++, transitionsBeanCop.getId());
            
            ps.executeUpdate();
            //transitionsBeanCop = Adaptador.obtenerAllDataFromTransitions(rs);         
        } catch (SQLException ex) {
           throw new CstF2Exception("Error al obtener el Usuario desde la base de datos");
        } finally {
            //odb.cerrarResultSet(rs);
            odb.cerrarStatement(ps);
            odb.cerrarConnection(conn);
        }
        //return transitionsBeanCop;
    }
}
